package com.yenlien.traceyun.business.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.business.dao.AddressDao;
import com.yenlien.traceyun.business.dao.TechnologyDao;
import com.yenlien.traceyun.business.entity.*;
import com.yenlien.traceyun.business.service.rest.control.ModelCropRestService;
import com.yenlien.traceyun.business.service.rest.usercenter.DepartmentRestService;
import com.yenlien.traceyun.business.service.rest.usercenter.FileRestService;
import com.yenlien.traceyun.common.crypt.CryptUtil;
import com.yenlien.traceyun.common.utils.Tools;
import com.yenlien.traceyun.webase.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.entity.ContentType;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK;

/**
 * @author: bigwangy@sina.com
 * @create: 2020-11-24
 * @description:
 **/

@Service
@Slf4j
public class UserService {

    @Autowired
    private DepartmentRestService departmentRestService;

    @Autowired
    private FileRestService fileService;

    @Autowired
    private TechnologyDao technologyDao;

    @Autowired
    private ModelCropRestService modelCropService;

    @Autowired
    private AddressDao addressDao;

    /**
     * 导出模板
     *
     * @param companyId
     */
    public JSONArray outTemplate(String companyId) {
        JSONObject result = (JSONObject) departmentRestService.chain(companyId);
        if (result.containsKey("code") && result.getString("code").equals("200")) {
            return result.getJSONArray("data");
        }
        throw new BusinessException("500", "返回状态码有误");
    }

    /**
     * 员工文件上传
     *
     * @param
     * @return
     */
    public List<UserBusiness> parseExcel(MultipartFile file, String companyId) {
        //获取文件上传名字
        String originalFilename = file.getOriginalFilename();
        //获取文件流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<UserBusiness> list = new ArrayList<>();
        // 判断文件是否是Excel(2003、2007)
        String suffix = originalFilename
                .substring(originalFilename.lastIndexOf("."), originalFilename.length());
        // Excel2003
        if (".xls".equals(suffix)) {
            POIFSFileSystem fileSystem = null;
            // 工作簿
            HSSFWorkbook workBook = null;
            try {
                fileSystem = new POIFSFileSystem(inputStream);
                workBook = new HSSFWorkbook(fileSystem);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            HSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
//                int rows = sheet.getLastRowNum();
            int rows = sheet.getPhysicalNumberOfRows();
            //获取列数
            int clos = sheet.getRow(1).getPhysicalNumberOfCells();
            for (int i = 2; i < rows; i++) {
                UserBusiness business = new UserBusiness();
                for (int j = 0; j < clos; j++) {//获取单元格中内容
                    String name = getCellValue(sheet.getRow(i).getCell(j++));
                    String phone = getCellValue(sheet.getRow(i).getCell(j++));
                    String password = getCellValue(sheet.getRow(i).getCell(j++));
                    String department = getCellValue(sheet.getRow(i).getCell(j++));
                    String idCard = getCellValue(sheet.getRow(i).getCell(j++));
                    business.setName(name);
                    business.setCompanyId(companyId);
                    business.setPhone(phone);
                    business.setPassword(CryptUtil.md5(password));
                    business.setDepartment(department);
                    business.setIdCard(String.valueOf(idCard));
                    business.setUserType(UserBusiness.UserType.COMMON);     //设置员工类型
                    business.setUsername(phone);
                    business.setCreatetime(new Date());
                }
                list.add(business);
            }
            // Excel2007
        } else if (".xlsx".equals(suffix)) {
            XSSFWorkbook workBook = null;
            try {
                workBook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            XSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
//                int rows = sheet.getLastRowNum();
            int rows = sheet.getPhysicalNumberOfRows();
            //获取列数
            int clos = sheet.getRow(1).getPhysicalNumberOfCells();
//                DecimalFormat df = new DecimalFormat("#");
            for (int i = 2; i < rows; i++) {
                UserBusiness business = new UserBusiness();
                for (int j = 0; j < clos; j++) {//获取单元格中内容
                    String name = getCellValue(sheet.getRow(i).getCell(j++));
                    String phone = getCellValue(sheet.getRow(i).getCell(j++));
                    String password = getCellValue(sheet.getRow(i).getCell(j++));
                    String department = getCellValue(sheet.getRow(i).getCell(j++));
                    String idCard = getCellValue(sheet.getRow(i).getCell(j++));
                    business.setName(name);
                    business.setCompanyId(companyId);
                    business.setPhone(phone);
                    business.setPassword(CryptUtil.md5(password));
                    business.setDepartment(department);
                    business.setIdCard(idCard);
                    business.setUserType(UserBusiness.UserType.COMMON);     //设置员工类型
                    business.setUsername(phone);
                    business.setCreatetime(new Date());
                }
                list.add(business);
            }
        }
        return list;
    }


    /**
     * 获取Excel内容
     *
     * @param sheet
     * @return
     */
    public List<UserBusiness> getContent(Sheet sheet) {
        List<UserBusiness> list = new ArrayList<UserBusiness>();
        // Excel数据总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        // 遍历数据行，略过标题行，从第二行开始
        for (int i = 1; i < rowCount; i++) {
            UserBusiness ub = new UserBusiness();
            Row row = sheet.getRow(i);
            int cellCount = row.getPhysicalNumberOfCells();
            // 遍历行单元格
            for (int j = 0; j <= cellCount; j++) {
                Cell cell = row.getCell(j);
                //判断单元格是否为空
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    throw new NullPointerException("单元格内容不能为空");
                }
            }
            list.add(ub);
        }
        return list;
    }


    /**
     * 创建excel表格
     *
     * @param
     */
    public void file(HttpServletResponse response, JSONArray departments) {

        List<String> departmentNames = new ArrayList<>();
        if (departments != null && departments.size() > 0) {
            for (int i = 0; i < departments.size(); i++) {
                departmentNames.add(departments.getJSONObject(i).getString("name"));
            }
        }

        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("员工表.xls", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //创建excel文件夹
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("员工信息");
        // 在sheet里创建第一行，参数为行索引excel的行()
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cellOne = row1.createCell(0);
        cellOne.setCellValue("用户信息");
        //设置行高40px
        row1.setHeight((short) (15.625 * 40));
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); //横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); //竖向居中
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));    // 合并单元格
        cellOne.setCellStyle(style);


        for (int i = 0; i < 6; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }

        HSSFRow row2 = sheet.createRow(1);
        row2.setRowStyle(style);
        row2.setHeight((short) (15.625 * 40));

        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row2.createCell(0);
        // 设置单元格内容
        cell.setCellValue("姓名(姓名)");
        HSSFCell cell1 = row2.createCell(1);
        cell1.setCellValue("手机号");


        HSSFCell cell2 = row2.createCell(2);
        cell2.setCellValue("登录密码(必填)");

        HSSFCell cell3 = row2.createCell(3);
        cell3.setCellValue("组织架构(必填)");


        HSSFCell cell4 = row2.createCell(4);
        cell4.setCellValue("身份证号");

        HSSFCell cell5 = row2.createCell(5);
        cell5.setCellValue("员工编码(必填)");

        //创建数组
        String[] strings = new String[departmentNames.size()];
        departmentNames.toArray(strings);

        DataValidationHelper helper = sheet.getDataValidationHelper();
//        DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
        //设置下拉控制的范围
        CellRangeAddressList regions = new CellRangeAddressList(2, 1024, 3, 3);

        DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
        // 绑定下拉框和作用区域
        DataValidation dataValidation = helper.createValidation(constraint, regions);

        //处理Excel兼容性问题
//        if(dataValidation instanceof XSSFDataValidation) {
//            dataValidation.setSuppressDropDownArrow(true);
//            dataValidation.setShowErrorBox(true);
//        }else {
//            dataValidation.setSuppressDropDownArrow(false);
//        }


        sheet.addValidationData(dataValidation);
        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 客户文件上传
     *
     * @param file
     * @return
     */
    public List<Client> clientParseExcel(MultipartFile file, String companyId) {
        //获取文件上传名字
        String originalFilename = file.getOriginalFilename();
        //获取文件流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<Client> list = new ArrayList<>();

        // 判断文件是否是Excel(2003、2007)
        String suffix = originalFilename
                .substring(originalFilename.lastIndexOf("."), originalFilename.length());
        // Excel2003
        if (".xls".equals(suffix)) {
            POIFSFileSystem fileSystem = null;
            // 工作簿
            HSSFWorkbook workBook = null;
            try {
                fileSystem = new POIFSFileSystem(inputStream);
                workBook = new HSSFWorkbook(fileSystem);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            HSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            //获取列数
            int clos = sheet.getRow(0).getPhysicalNumberOfCells();
            DecimalFormat df = new DecimalFormat("#");
            for (int i = 1; i <= rows; i++) {
                Client client = new Client();
                for (int j = 0; j <= clos; j++) {//获取单元格中内容
                    String clientName = getCellValue(sheet.getRow(i).getCell(j++));
                    String level = getCellValue(sheet.getRow(i).getCell(j++));
                    String area = getCellValue(sheet.getRow(i).getCell(j++));
                    String address = getCellValue(sheet.getRow(i).getCell(j++));
                    String settleType = getCellValue(sheet.getRow(i).getCell(j++));
                    String userName = getCellValue(sheet.getRow(i).getCell(j++));
                    String position = getCellValue(sheet.getRow(i).getCell(j++));
                    String phone = getCellValue(sheet.getRow(i).getCell(j++));
                    String telephone = getCellValue(sheet.getRow(i).getCell(j++));
                    String email = getCellValue(sheet.getRow(i).getCell(j++));

                    client.setCompanyId(companyId);
                    client.setClientName(clientName);
                    client.setLevel(level);
                    client.setArea(queryAddress(area));
                    client.setAddress(address);
                    client.setSettleType(settleType);
                    client.setUserName(userName);
                    client.setPosition(position);
                    client.setPhone(phone);
                    client.setTelephone(telephone);
                    client.setEmail(email);
                }
                list.add(client);
            }
            // Excel2007
        } else if (".xlsx".equals(suffix)) {
            XSSFWorkbook workBook = null;
            try {
                workBook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            XSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            //获取列数
            int clos = sheet.getRow(0).getPhysicalNumberOfCells();
            DecimalFormat df = new DecimalFormat("#");
            for (int i = 1; i <= rows; i++) {
                Client client = new Client();
                for (int j = 0; j <= clos; j++) {//获取单元格中内容
                    String clientName = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String level = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String area = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String address = sheet.getRow(i).getCell(j++).getStringCellValue();
                    //BigDecimal.valueOf((sheet.getRow(i).getCell(j++).getNumericCellValue())).toString();
                    String settleType = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String userName = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String position = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String phone = df.format(sheet.getRow(i).getCell(j++).getNumericCellValue());
                    String telephone = df.format(sheet.getRow(i).getCell(j++).getNumericCellValue());
                    String email = sheet.getRow(i).getCell(j++).getStringCellValue();
                    client.setCompanyId(companyId);
                    client.setClientName(clientName);
                    client.setLevel(level);
                    client.setArea(queryAddress(area));
                    client.setAddress(address);
                    client.setSettleType(settleType);
                    client.setUserName(userName);
                    client.setPosition(position);
                    client.setPhone(phone);
                    client.setTelephone(telephone);
                    client.setEmail(email);
                }
                list.add(client);
            }
        }

        return list;

    }

    /**
     * 客户文件下载
     *
     * @param response
     */
    public void fileClient(HttpServletResponse response) {
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("客户表.xls", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //创建excel文件夹
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("客户信息表");
        // 在sheet里创建第一行，参数为行索引excel的行()
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("客户名称");
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellValue("客户级别");
        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellValue("所在地区");
        HSSFCell cell3 = row1.createCell(3);
        cell3.setCellValue("收货地址");
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellValue("结算方式");
        HSSFCell cell5 = row1.createCell(5);
        cell5.setCellValue("姓名");
        HSSFCell cell6 = row1.createCell(6);
        cell6.setCellValue("职位");
        HSSFCell cell7 = row1.createCell(7);
        cell7.setCellValue("手机号码");
        HSSFCell cell8 = row1.createCell(8);
        cell8.setCellValue("电话号码");
        HSSFCell cell9 = row1.createCell(9);
        cell9.setCellValue("邮箱");

        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 供应商文件上传
     *
     * @param file
     * @return
     */

    public List<Supplier> supplierParseExcel(MultipartFile file, String companyId) {
        //获取文件上传名字
        String originalFilename = file.getOriginalFilename();
        //获取文件流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<Supplier> list = new ArrayList<>();

        // 判断文件是否是Excel(2003、2007)
        String suffix = originalFilename
                .substring(originalFilename.lastIndexOf("."), originalFilename.length());
        // Excel2003
        if (".xls".equals(suffix)) {
            POIFSFileSystem fileSystem = null;
            // 工作簿
            HSSFWorkbook workBook = null;
            try {
                fileSystem = new POIFSFileSystem(inputStream);
                workBook = new HSSFWorkbook(fileSystem);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            HSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            //获取列数
            int clos = sheet.getRow(0).getPhysicalNumberOfCells();
            DecimalFormat df = new DecimalFormat("#");
            for (int i = 1; i <= rows; i++) {
                Supplier supplier = new Supplier();
                for (int j = 0; j <= clos; j++) {//获取单元格中内容

                    HSSFCell cell = sheet.getRow(i).getCell(j++);
                    String cellTypeEnum = cell.getCellTypeEnum().name();
                    String supplierName = "";
                    if (cellTypeEnum.equals("STRING")) {
                        supplierName = cell.getStringCellValue();
                    }

                    HSSFCell cell1 = sheet.getRow(i).getCell(j++);      //用户名
                    String cellTypeEnum1 = cell1.getCellTypeEnum().name();
                    String userName = "";
                    if (cellTypeEnum1.equals("STRING")) {
                        userName = cell1.getStringCellValue();
                    }

                    HSSFCell cell2 = sheet.getRow(i).getCell(j++);          //电话
                    String cellTypeEnum2 = cell2.getCellTypeEnum().name();
                    String phone = "";
                    if (cellTypeEnum2.equals("STRING")) {
                        phone = cell2.getStringCellValue();
                    } else if (cellTypeEnum2.equals("NUMERIC")) {
                        phone = df.format(cell2.getNumericCellValue());
                    }

                    HSSFCell cell3 = sheet.getRow(i).getCell(j++);
                    String cellTypeEnum3 = cell3.getCellTypeEnum().name();
                    String area = "";
                    if (cellTypeEnum3.equals("STRING")) {
                        area = cell3.getStringCellValue();
                    }

                    HSSFCell cell4 = sheet.getRow(i).getCell(j++);
                    String cellTypeEnum4 = cell4.getCellTypeEnum().name();
                    String address = "";
                    if (cellTypeEnum4.equals("STRING")) {
                        address = cell4.getStringCellValue();
                    }

                    supplier.setCompanyId(companyId);
                    supplier.setSupplierName(supplierName);
                    supplier.setUserName(userName);
                    supplier.setPhone(phone);

                    supplier.setArea(queryAddress(area));
                    supplier.setAddress(address);
                }
                list.add(supplier);
            }
            // Excel2007
        } else if (".xlsx".equals(suffix)) {
            XSSFWorkbook workBook = null;
            try {
                workBook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            XSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            //获取列数
            int clos = sheet.getRow(0).getPhysicalNumberOfCells();
            DecimalFormat df = new DecimalFormat("#");
            for (int i = 1; i <= rows; i++) {
                Supplier supplier = new Supplier();
                for (int j = 0; j <= clos; j++) {//获取单元格中内容
                    String supplierName = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String userName = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String phone = df.format(sheet.getRow(i).getCell(j++).getNumericCellValue());
                    String area = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String address = sheet.getRow(i).getCell(j++).getStringCellValue();
                    supplier.setCompanyId(companyId);
                    supplier.setSupplierName(supplierName);
                    supplier.setUserName(userName);
                    supplier.setPhone(phone);
                    supplier.setArea(queryAddress(area));
                    supplier.setAddress(address);
                }
                list.add(supplier);
            }
        }

        return list;

    }

    public String queryAddress(String area) {
        StringJoiner sj = new StringJoiner(",");
        if (area.contains("/")) {
            String[] split = area.split("/");
            for (int i = 0; i < split.length; i++) {
                Address byName = addressDao.findByName(split[i]);
                if (byName != null) {
                    int id = byName.getId();
                    sj.add(String.valueOf(id));
                }
            }
        }
        return sj.toString();
    }

    /**
     * 供应商文件下载
     *
     * @param response
     */
    public void fileSupplier(HttpServletResponse response) {
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("供应商表.xls", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //创建excel文件夹
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("供应商信息表");
        // 在sheet里创建第一行，参数为行索引excel的行()
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("供应商名称");
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellValue("联系人姓名");
        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellValue("联系方式");
        HSSFCell cell3 = row1.createCell(3);
        cell3.setCellValue("所在地区");
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellValue("详细地址");
        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取关联品名集合
     */
    public JSONArray toCraftDataV(HttpServletRequest req) {
        JSONObject param = new JSONObject();
        param.put("companyId", req.getParameter("companyId"));
        param.put("appId", req.getParameter("appId"));
        JSONObject jsonObject = (JSONObject) modelCropService.findByCompany(param);
        if (jsonObject.containsKey("code") && jsonObject.getString("code").equals("200")) {
            return jsonObject.getJSONArray("data");
        }
        throw new BusinessException("500", "调用 /control/model/crop/loadbind 失败");
    }

    /**
     * 创建工艺工序 excel 模板表格
     *
     * @param
     */
    public void toCraft(HttpServletResponse response, JSONArray array) {
        // 作物名称集合,用于限定 关联品名 单元格的输入内容
        List<String> strings = new ArrayList<>();
        if (array != null && array.size() > 0) {
            for (int i = 0; i < array.size(); i++) {
                strings.add(array.getJSONObject(i).getString("name"));
            }
        }
        String[] names = new String[strings.size()];
        strings.toArray(names);

        // 生成 Excel 文件
        genCraftXls(response, names);
    }


    public void genCraftXls(HttpServletResponse response, String[] names) {
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("工艺工序表.xls", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); //横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); //竖向居中

        // 第一个sheet与第二个sheet里的数据通过关联ID进行绑定，通过这种方式实现一对多
        HSSFSheet sheetOne = wb.createSheet("工艺");
        HSSFRow row1 = sheetOne.createRow(0);

        HSSFCell cell0 = row1.createCell(0);
        cell0.setCellValue("工艺名称");
        cell0.setCellStyle(style);
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellValue("关联品名");
        cell1.setCellStyle(style);
        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellValue("执行标准(jpg、png、gif格式，60x60像素，不超过2MB)");
        cell2.setCellStyle(style);
        HSSFCell cell3 = row1.createCell(3);
        cell3.setCellValue("与具体顺序工艺顺序绑定的关联ID");
        cell3.setCellStyle(style);

        // 第二个sheet  多条数据对应sheet1 里的一条
        HSSFSheet sheetTwo = wb.createSheet("具体工艺顺序");
        HSSFRow row10 = sheetTwo.createRow(0);

        HSSFCell cell4 = row10.createCell(0);
        cell4.setCellValue("排序");
        cell4.setCellStyle(style);
        HSSFCell cell5 = row10.createCell(1);
        cell5.setCellValue("标准名称");
        cell5.setCellStyle(style);
        HSSFCell cell6 = row10.createCell(2);
        cell6.setCellValue("检测标准文件");
        cell6.setCellStyle(style);
        HSSFCell cell7 = row10.createCell(3);
        cell7.setCellValue("与工艺绑定的关联ID(通过该ID实现多个工艺顺序绑定一个工艺)");
        cell7.setCellStyle(style);

        //设置行高40px
        row1.setHeight((short) (15.625 * 40));
        row10.setHeight((short) (15.625 * 40));

        for (int i = 0; i < 4; i++) {
            sheetOne.setColumnWidth(i, 55 * 256);
            sheetTwo.setColumnWidth(i, 55 * 256);
        }

        DataValidationHelper helper = sheetOne.getDataValidationHelper();

        //设置下拉控制的范围
        CellRangeAddressList regions = new CellRangeAddressList(1, 1024, 1, 1);

        DataValidationConstraint constraint = helper.createExplicitListConstraint(names);
        // 绑定下拉框和作用区域
        DataValidation dataValidation = helper.createValidation(constraint, regions);

        //处理Excel兼容性问题
//        if(dataValidation instanceof XSSFDataValidation) {
//            dataValidation.setSuppressDropDownArrow(true);
//            dataValidation.setShowErrorBox(true);
//        }else {
//            dataValidation.setSuppressDropDownArrow(false);
//        }

        sheetOne.addValidationData(dataValidation);
        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param file
     * @param companyId
     */
    public void importCraft(MultipartFile file, String companyId) throws IOException {
        String originalFilename = file.getOriginalFilename();   //获取文件上传名字
        InputStream inputStream1 = file.getInputStream();                          //获取文件流
        InputStream inputStream2 = file.getInputStream();                          //获取文件流
        List<Technology> technologies = null;
        List<Technology> technologyList = null;
        String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));   // 判断文件是否是Excel(2003、2007)
        if (".xls".equals(suffix)) {    // 直接改后缀名会导致无法解析   new HSSFWorkbook(inputStream1) 使用过后流就会自动关闭
            technologies = parseOne(new HSSFWorkbook(inputStream1), "2003");
            technologyList = parseTwo(new HSSFWorkbook(inputStream2));
        } else if (".xlsx".equals(suffix)) {
            technologies = parseOne(new XSSFWorkbook(inputStream1), "2007");
            technologyList = parseTwo(new XSSFWorkbook(inputStream2));
        }
//        inputStream.close();
        if (technologies != null && technologies.size() > 0) {
            checkAllIsSuccess(technologyDao.saveAll(technologies));
        }
        if (technologyList == null || technologyList.size() == 0) {
            return;
        }
        // 进行数据绑定
        for (Technology technology : technologies) {
            for (Technology tech : technologyList) {
                if (technology.getRelationId() == tech.getRelationId()) {
                    tech.setpId(technology.getId());
                }
            }
        }
        // 插入附属工艺数据
        checkAllIsSuccess(technologyDao.saveAll(technologyList));
    }


    /**
     * 解析第一页sheet
     *
     * @param workbook
     * @return
     */
    public List<Technology> parseOne(Workbook workbook, String type) throws IOException {
        Sheet sheet01 = workbook.getSheetAt(0);
        // 先取第一个Sheet页内容
        int rowTotal = sheet01.getPhysicalNumberOfRows();     // 行数
        int colTotal = sheet01.getRow(0).getPhysicalNumberOfCells();
        if (rowTotal == 0 || colTotal == 0) {
            return null;
        }
//        Map<String, PictureData> pictures = null;
        Map<String, HSSFPictureData> picturesHSSF = null;
        Map<String, XSSFPictureData> picturesXSSF = null;

        if ("2003".equals(type)) {
            picturesHSSF = getPictures((HSSFSheet) sheet01);
        } else if ("2007".equals(type)) {
            picturesXSSF = getPictures((XSSFSheet) sheet01);
        }
//        Map<String, PictureData> pictures = getPictures(sheet01);    // 取出图片和位置
        List<Technology> technologies = new ArrayList<>();
        for (int i = 1; i < rowTotal; i++) {
            Row row = sheet01.getRow(i);
            if (row == null) {
                return null;
            }
            Technology technology = new Technology();
            for (int j = 0; j < colTotal; j++) {
                technology.setName(row.getCell(j).getStringCellValue());
                technology.setProductName(row.getCell(++j).getStringCellValue());
                if (++j == 2) {
                    String key = i + "-" + j;
                    if (picturesHSSF != null) {
                        //fileRestService 可以一次上传多张，用 ; 号隔开的，但不能回填多个图片名称
                        technology.setStandardsImg(fileService.upload(
                                new MockMultipartFile("A8.jpg", "A8.jpg", ContentType.APPLICATION_OCTET_STREAM.toString(), new ByteArrayInputStream(picturesHSSF.get(key).getData())))
                        );
                    } else if (picturesXSSF != null) {
                        technology.setStandardsImg(fileService.upload(
                                new MockMultipartFile("A8.jpg", "A8.jpg", ContentType.APPLICATION_OCTET_STREAM.toString(), new ByteArrayInputStream(picturesXSSF.get(key).getData())))
                        );
                    }
                }
                technology.setRelationId((int) row.getCell(++j).getNumericCellValue());
                technology.setCreatetime(new Date());
                technologies.add(technology);
            }
        }
        return technologies;
    }

    /**
     * 解析第二页sheet
     *
     * @return
     */
    public List<Technology> parseTwo(Workbook workbook) {
        List<Technology> technologyList = new ArrayList<>();
        Sheet sheet02 = workbook.getSheet("具体工艺顺序");
        int rowTotal2 = sheet02.getPhysicalNumberOfRows();     // 行数
        int colTotal2 = sheet02.getRow(0).getPhysicalNumberOfCells();
        if (rowTotal2 == 0 || colTotal2 == 0) {
            return null;
        }
        for (int i = 1; i < rowTotal2; i++) {
            Row row = sheet02.getRow(i);
            if (row == null) {
                return null;
            }
            Technology technology = new Technology();
            for (int j = 0; j < colTotal2; j++) {
                technology.setSort(String.valueOf((int) row.getCell(j).getNumericCellValue()));
                technology.setStandards(getCellValue(row.getCell(++j)));
                technology.setSpecification(getCellValue(row.getCell(++j)));
                technology.setRelationId((int) row.getCell(++j).getNumericCellValue());
                technology.setCreatetime(new Date());
                technologyList.add(technology);
            }
        }
        return technologyList;
    }


    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, HSSFPictureData> getPictures(HSSFSheet sheet) {
        Map<String, HSSFPictureData> map = new HashMap<>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                HSSFPictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, XSSFPictureData> getPictures(XSSFSheet sheet) throws IOException {
        Map<String, XSSFPictureData> map = new HashMap<>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }


    public void checkAllIsSuccess(List<Technology> lists) {
        for (Technology technology : lists) {
            if (technology.getId() == null) {
                log.error("导入工艺工序时插入数据失败, 数据：{}", technology);
                throw new BusinessException("A0001", "插入工艺工序数据失败");
            }
        }
    }

    /**
     * 获取 cell 的值,excel获取单元格类型
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String value = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                //如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    value = Tools.DATE_TIME_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    break;
                } else {
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case FORMULA:
                value = cell.getCellFormula() + "";
                break;
            case BLANK:
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
            case ERROR:
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

}
